package com.huaxia.dao.deptLevels;

import com.huaxia.pojo.deptLevels.DeptBM;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/04 9:07
 */
public interface DeptBMMapper {

    @Select("select a.branchsname,a.deptname3,a.issueMoney3,a.agentNum from(\n" +
            "select a.branchsname,a.deptname3,b.issueMoney3,a.agentNum from(\n" +
            "select sb.branchsname,a.deptcode3,a.deptname3,b.agentNum  from SD_BRANCH sb right join(\n" +
            "select t.branchcode,t.deptcode3,t.deptname3 from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('BM','SBM') and t.agentstate='在职')a on sb.branchcode=a.branchcode\n" +
            "left join (\n" +
            "select  t.branchcode,t.deptcode3,t.deptname3, count(*) agentNum  from d_agent_province_yx2 t where deptname3 like '%部' \n" +
            "and t.agentstate='在职' group by t.deptcode3,t.deptname3,t.branchcode) b on a.deptcode3=b.deptcode3\n" +
            ")a,\n" +
            "(\n" +
            "select a.deptcode3,round(NVL(a.monthInSure-b.monthYt,0),0)issueMoney3 from(\n" +
            "select t.deptcode3,SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day t where  deptname3 like '%部' and\n" +
            "TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode3\n" +
            ")a left join (select t.deptcode3,SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day t where   deptname3 like '%部' and\n" +
            "TO_CHAR(yt_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode3)b on a.deptcode3=b.deptcode3\n" +
            ")b where a.deptcode3=b.deptcode3 order by b.issueMoney3 DESC)a where rownum <= '30'")
    List<DeptBM> getDeptBM();
}
